CREATE DATABASE aa_CAMERA
GO
USE  aa_CAMERA
GO

CREATE TABLE category_event(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
	content_template text not null,
	del_flg int not null
)


go 

CREATE TABLE event(
	id int IDENTITY(1,1) not null PRIMARY KEY,
	id_ce int not null FOREIGN KEY REFERENCES category_event(Id),
	title nvarchar(200) not null,
	start_date datetime not null,
	end_date datetime not null,
	content text not null,
	del_flg int not null
)

go 

CREATE TABLE menu_l1(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
	position int not null,
	del_flg int not null
)

go

CREATE TABLE menu_l2(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	id_l1 int not null FOREIGN KEY REFERENCES menu_l1(Id),
	name nvarchar(100) not null,
	position int not null,
	del_flg int not null
)

go

CREATE TABLE menu_l3(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	id_l2 int not null FOREIGN KEY REFERENCES menu_l2(Id),
	name nvarchar(100) not null,
	position int not null,
	del_flg int not null
)

go

CREATE TABLE product(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	id_l1 int not null FOREIGN KEY REFERENCES menu_l1(Id),
	id_l2 int not null FOREIGN KEY REFERENCES menu_l2(Id),
	id_l3 int not null FOREIGN KEY REFERENCES menu_l3(Id),
	position1 int not null,
	position2 int not null,
	position3 int not null,
	newpro_flag int,
	prosales_flag int,
	prosale_flag int,
	name nvarchar(100) not null,
	warranty nvarchar(100) not null,
	make_in nvarchar(100) not null,
	price float not null,
	sale int,
	inf_pro text,
	int_pro text,
	del_flg int not null
)

CREATE TABLE image(
	id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	name nvarchar(100) not null,
)


----------------------------------------------------
USE [aa_CAMERA]
GO
/****** StoredProcedure [dbo].[spEventInsert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spEventInsert]
	@id_ce int,
	@title nvarchar(200),
	@title2 nvarchar(1000),
	@image nvarchar(200),
	@start_date datetime,
	@end_date datetime,
	@content text
AS
BEGIN
	INSERT INTO Event(id_ce, title, title2, image, start_date, end_date, content, del_flg)
	VALUES(@id_ce, @title, @title2, @image, @start_date, @end_date, @content, 0)
END


/****** Object:  StoredProcedure [dbo].[spEventSearchById_ce]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spEventSearchById_ce]
	@id_ce int
AS
BEGIN
	SELECT *
	  FROM event E
	  WHERE E.del_flg = 0
		AND E.id_ce =  @id_ce 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [spEventSearchById]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spEventSearchById]
	@id int
AS
BEGIN
	SELECT *
	  FROM event E
	  WHERE E.del_flg = 0
		AND E.id =  @id 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [dbo].[spEventUpdate]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spEventUpdate]
	@id int,
	@id_ce int,
	@title nvarchar(200),
	@title2 nvarchar(1000),
	@image nvarchar(200),
	@start_date datetime,
	@end_date datetime,
	@content text
AS
BEGIN
	UPDATE event
	SET id_ce = @id_ce, title = @title, title2 = @title2, image = @image, start_date = @start_date, end_date = @end_date, content = @content
	WHERE id = @id
END

/****** Object:  StoredProcedure [dbo].[spEventDelete]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spEventDelete]
	@id int
AS
BEGIN
	UPDATE event
	SET  del_flg = 1
	WHERE id = @id
END


-----------------------19/03-category_event--------------------------
GO
/****** StoredProcedure [dbo].[spCategory_eventInsert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spCategory_eventInsert]
	@name nvarchar(100),
	@content_template text
AS
BEGIN
	INSERT INTO Category_event(name, content_template, del_flg)
	VALUES(@name, @content_template, 0)
END

/****** Object:  StoredProcedure [spCategory_eventSearchById]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spCategory_eventSearchById]
	@id int
AS
BEGIN
	SELECT *
	  FROM category_event E
	  WHERE E.del_flg = 0
		AND E.id =  @id 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [dbo].[spCategory_eventUpdate]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spCategory_eventUpdate]
	@id int,
	@name nvarchar(100),
	@content_template text
AS
BEGIN
	UPDATE category_event
	SET name = @name, content_template = @content_template
	WHERE id = @id
END

/****** Object:  StoredProcedure [dbo].[spCategory_eventDelete]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spCategory_eventDelete]
	@id int
AS
BEGIN
	UPDATE category_event
	SET  del_flg = 1
	WHERE id = @id
END

-----------------------19/03-menu_l1--------------------------
GO
/****** StoredProcedure [dbo].[spMenu_l1Insert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l1Insert]
	@name nvarchar(100),
	@position int
AS
BEGIN
	INSERT INTO menu_l1(name, position, del_flg)
	VALUES(@name, @position, 0)
END

/****** Object:  StoredProcedure [spMenu_l1SearchById]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l1SearchById]
	@id int
AS
BEGIN
	SELECT *
	  FROM menu_l1 E
	  WHERE E.del_flg = 0
		AND E.id =  @id 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [spMenu_l1Search]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l1Search]
AS
BEGIN
	SELECT *
	  FROM menu_l1 E
	  WHERE E.del_flg = 0
END

/****** Object:  StoredProcedure [dbo].[spMenu_l1Update]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l1Update]
	@id int,
	@name nvarchar(100),
	@position int
AS
BEGIN
	UPDATE menu_l1
	SET name = @name, position = @position
	WHERE id = @id
END

/****** Object:  StoredProcedure [dbo].[spMenu_l1Delete]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l1Delete]
	@id int
AS
BEGIN
	UPDATE menu_l1
	SET  del_flg = 1
	WHERE id = @id
END

---------------------@menu_l2-------------------------------
GO
/****** StoredProcedure [dbo].[spMenu_l2Insert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l2Insert]
	@id_l1 int,
	@name nvarchar(100),
	@position int
AS
BEGIN
	INSERT INTO menu_l2(id_l1, name, position, del_flg)
	VALUES(@id_l1, @name, @position, 0)
END


/****** Object:  StoredProcedure [dbo].[spMenu_l2SearchById_ce]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l2SearchById_l1]
	@id_l1 int
AS
BEGIN
	SELECT *
	  FROM menu_l2 E
	  WHERE E.del_flg = 0
		AND E.id_l1 =  @id_l1
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [spMenu_l2SearchById]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l2SearchById]
	@id int
AS
BEGIN
	SELECT *
	  FROM menu_l2 E
	  WHERE E.del_flg = 0
		AND E.id =  @id 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [dbo].[spMenu_l2Update]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l2Update]
	@id int,
	@id_l1 int,
	@name nvarchar(100),
	@position int
AS
BEGIN
	UPDATE menu_l2
	SET id_l1 = @id_l1, name = @name, position = @position
	WHERE id = @id
END

/****** Object:  StoredProcedure [dbo].[spMenu_l2Delete]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l2Delete]
	@id int
AS
BEGIN
	UPDATE menu_l2
	SET  del_flg = 1
	WHERE id = @id
END

---------------------@menu_l3-------------------------------
GO
/****** StoredProcedure [dbo].[spMenu_l3Insert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l3Insert]
	@id_l2 int,
	@name nvarchar(100),
	@position int
AS
BEGIN
	INSERT INTO menu_l3(id_l2, name, position, del_flg)
	VALUES(@id_l2, @name, @position, 0)
END


/****** Object:  StoredProcedure [dbo].[spMenu_l3SearchById_ce]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l3SearchById_l2]
	@id_l2 int
AS
BEGIN
	SELECT *
	  FROM menu_l3 E
	  WHERE E.del_flg = 0
		AND E.id_l2 =  @id_l2
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [spMenu_l3SearchById]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l3SearchById]
	@id int
AS
BEGIN
	SELECT *
	  FROM menu_l3 E
	  WHERE E.del_flg = 0
		AND E.id =  @id 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [dbo].[spMenu_l3Update]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l3Update]
	@id int,
	@id_l2 int,
	@name nvarchar(100),
	@position int
AS
BEGIN
	UPDATE menu_l3
	SET id_l2 = @id_l2, name = @name, position = @position
	WHERE id = @id
END

/****** Object:  StoredProcedure [dbo].[spMenu_l3Delete]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l3Delete]
	@id int
AS
BEGIN
	UPDATE menu_l3
	SET  del_flg = 1
	WHERE id = @id
END

---------------------@product-------------------------------
GO
/****** StoredProcedure [dbo].[spProductInsert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductInsert]
	@id_l1 int,
	@id_l2 int,
	@id_l3 int,
	@image nvarchar(200),
	@position1 int,
	@position2 int,
	@position3 int,
	@newpro_flag int,
	@prosales_flag int,
	@prosale_flag int,
	@name nvarchar(100),
	@warranty nvarchar(100),
	@make_in nvarchar(100),
	@price float,
	@sale int,
	@inf_pro text,
	@int_pro text
AS
BEGIN
	INSERT INTO product(id_l1, id_l2, id_l3, image, position1, position2, position3, newpro_flag, prosales_flag, prosale_flag, name, warranty, make_in, price, sale, inf_pro, int_pro, del_flg)
	VALUES(@id_l1, @id_l2, @id_l3, @image, @position1, @position2, @position3, @newpro_flag, @prosales_flag, @prosale_flag, @name, @warranty, @make_in, @price, @sale, @inf_pro, @int_pro, 0)
END


/****** Object:  StoredProcedure [dbo].[spProductSearchById_l1]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductSearchById_l1]
	@id_l1 int
AS
BEGIN
	SELECT *
	  FROM product E
	  WHERE E.del_flg = 0
		AND E.id_l1 =  @id_l1
	 ORDER BY E.position1 DESC
END

/****** Object:  StoredProcedure [dbo].[spProductSearchById_l2]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductSearchById_l2]
	@id_l2 int
AS
BEGIN
	SELECT *
	  FROM product E
	  WHERE E.del_flg = 0
		AND E.id_l2 =  @id_l2
	 ORDER BY E.position2 DESC
END

/****** Object:  StoredProcedure [dbo].[spProductSearchById_l3]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductSearchById_l3]
	@id_l3 int
AS
BEGIN
	SELECT *
	  FROM product E
	  WHERE E.del_flg = 0
		AND E.id_l3 =  @id_l3
	 ORDER BY E.position3 DESC
END

/****** Object:  StoredProcedure [spProductSearchById]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductSearchById]
	@id int
AS
BEGIN
	SELECT *
	  FROM product E
	  WHERE E.del_flg = 0
		AND E.id =  @id 
	 ORDER BY E.id DESC
END

/****** Object:  StoredProcedure [dbo].[spProductUpdate]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductUpdate]
	@id int,	
	@id_l1 int,
	@id_l2 int,
	@id_l3 int,
	@image nvarchar(200),
	@position1 int,
	@position2 int,
	@position3 int,
	@newpro_flag int,
	@prosales_flag int,
	@prosale_flag int,
	@name nvarchar(100),
	@warranty nvarchar(100),
	@make_in nvarchar(100),
	@price float,
	@sale int,
	@inf_pro text,
	@int_pro text
AS
BEGIN
	UPDATE product
	SET id_l1 = @id_l1, id_l2 = @id_l2, id_l3 = @id_l3, image = @image, position1 = @position1, position2 = @position2, position3 = @position3, newpro_flag = @newpro_flag, prosales_flag = @prosales_flag, prosale_flag = @prosale_flag, name = @name, warranty = @warranty, make_in = @make_in, price = @price, sale = @sale, inf_pro = @inf_pro, int_pro = @int_pro
	WHERE id = @id
END

/****** Object:  StoredProcedure [dbo].[spProductDelete]    Script Date: 3/18/2014 12:01:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductDelete]
	@id int
AS
BEGIN
	UPDATE product
	SET  del_flg = 1
	WHERE id = @id
END


-------------------------------------------------------------------
USE [aa_CAMERA]
GO
/****** StoredProcedure [dbo].[spEventInsert]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

-----------------------19/03-menu_l2--------------------------
GO
/****** Object:  StoredProcedure [spMenu_l2Search]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l2Search]
AS
BEGIN
	SELECT *
	  FROM menu_l2 E
	  WHERE E.del_flg = 0
END

-----------------------19/03-menu_l3--------------------------
GO
/****** Object:  StoredProcedure [spMenu_l3Search]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spMenu_l3Search]
AS
BEGIN
	SELECT *
	  FROM menu_l3 E
	  WHERE E.del_flg = 0
END

/****** Object:  StoredProcedure [spProductSearch]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spProductSearch]
AS
BEGIN
	SELECT *
	  FROM product E
	  WHERE E.del_flg = 0
END

/****** Object:  StoredProcedure [spCategory_eventSearch]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spCategory_eventSearch]
AS
BEGIN
	SELECT *
	  FROM category_event E
	  WHERE E.del_flg = 0
END

/****** Object:  StoredProcedure [spEventSearch]    Script Date: 3/17/2014 11:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [spEventSearch]
AS
BEGIN
	SELECT *
	  FROM event E
	  WHERE E.del_flg = 0
END